package dao

import (
	"strconv"
	"web08_35_tushu/model"
	"web08_35_tushu/utils"
)

//GetBooks 获取所有图书，就不需要传递参数，调用就可以获取
func GetBooks() ([]*model.Book, error) {
	//写sql语句
	sqlStr := "SELECT id, title, author, price, sales, stock, img_path FROM books"
	//执行
	rows, err := utils.Db.Query(sqlStr)
	if err != nil {
		return nil, err
	}
	var books []*model.Book
	for rows.Next() {
		book := &model.Book{}
		//字段赋值，这里传的是地址
		rows.Scan(&book.ID, &book.Title, &book.Author, &book.Price, &book.Sales, &book.Stock, &book.ImgPath)
		//把book添加到books中
		books = append(books, book)
	}
	return books, nil
}

//GetBookByID 获取一本图书，根据图书id从数据库中查询出一本书的数据
func GetBookByID(bookID string) (*model.Book, error) {
	//sql语句
	sqlStr := "select * from books where id = ?"
	//执行sql
	row := utils.Db.QueryRow(sqlStr, bookID)
	//创建个book结构体
	book := &model.Book{}
	//读取数据保存到结构体里
	row.Scan(&book.ID, &book.Title, &book.Author, &book.Price, &book.Sales, &book.Stock, &book.ImgPath)
	return book, nil
}

//AddBook 添加图书
func AddBook(b *model.Book) error {
	//写插入的sql语句
	sqlStr := "insert into books(title,author,price,sales,stock,img_path) values(?,?,?,?,?,?)"
	//执行
	_, err := utils.Db.Exec(sqlStr, b.Title, b.Author, b.Price, b.Sales, b.Stock, b.ImgPath)
	if err != nil {
		return err
	}
	return nil
}

//DeleteBook 根据图书id删除图书
func DeleteBook(bookID string) error {
	//sql语句
	sqlStr := "delete from books where id = ?"
	_, err := utils.Db.Exec(sqlStr, bookID)
	if err != nil {
		return err
	}
	return nil
}

//UpdateBook 根据图书id更新图书信息
func UpdateBook(b *model.Book) error {
	//sql语句
	sqlStr := "update books set title=?,author=?,price=?,sales=?,stock=? where id=?"
	//执行sql语句
	_, err := utils.Db.Exec(sqlStr, b.Title, b.Author, b.Price, b.Sales, b.Stock, b.ID)
	if err != nil {
		return err
	}
	return nil
}

//GetPageBooks 通过页码，获取每页数据
func GetPageBooks(pageNo string) (*model.Page, error) {
	//转换传入的数据
	iPageNo, err := strconv.ParseInt(pageNo, 10, 64) //转换的字符串pageNo，进制数10，类型int64
	if err != nil {
		return nil, err
	}
	//sql查询有多少条数据
	sqlStr := "select count(*) from books"
	//创建变量接收总条数
	var totalRecord int64
	row := utils.Db.QueryRow(sqlStr)
	row.Scan(&totalRecord)
	//根据总条数得到分页总数
	var pageSize int64 = 4 //定义每页多少条，这里之所以没有:=是因为Page那里是int64,而:=是int
	var totalPageNo int64
	if totalRecord%pageSize == 0 { //这里取余，为0直接赋值，不为0就+1
		totalPageNo = totalRecord / pageSize
	} else {
		totalPageNo = totalRecord/pageSize + 1
	}
	//根据传入页数查询当前页数据
	sqlStr2 := "SELECT id, title, author, price, sales, stock, img_path FROM books limit ?,?"
	rows, err := utils.Db.Query(sqlStr2, (iPageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	var books []*model.Book //定义book切片存放当前页数据
	for rows.Next() {
		book := &model.Book{} //单条信息读取
		rows.Scan(&book.ID, &book.Title, &book.Author, &book.Price, &book.Sales, &book.Stock, &book.ImgPath)
		books = append(books, book) //单条信息写入
	}

	//最后把书籍信息及相关页数总数信息写入page结构体
	page := &model.Page{
		Books:       books,
		PageNo:      iPageNo,
		PageSize:    pageSize,
		TotalPageNo: totalPageNo,
		TotalRecord: totalRecord,
	}
	return page, nil
}

//GetPageBooksByPrice 通过页码和价格范围，获取每页数据
func GetPageBooksByPrice(pageNo string, minPrice string, maxPrice string) (*model.Page, error) {
	//转换传入的数据
	iPageNo, err := strconv.ParseInt(pageNo, 10, 64) //转换的字符串pageNo，进制数10，类型int64
	if err != nil {
		return nil, err
	}
	//sql查询有多少条数据
	sqlStr := "select count(*) from books where price between ? and ?"
	//创建变量接收总条数
	var totalRecord int64
	row := utils.Db.QueryRow(sqlStr, minPrice, maxPrice)
	row.Scan(&totalRecord)
	//根据总条数得到分页总数
	var pageSize int64 = 4 //定义每页多少条，这里之所以没有:=是因为Page那里是int64,而:=是int
	var totalPageNo int64
	if totalRecord%pageSize == 0 { //这里取余，为0直接赋值，不为0就+1
		totalPageNo = totalRecord / pageSize
	} else {
		totalPageNo = totalRecord/pageSize + 1
	}
	//根据传入页数查询当前页数据
	sqlStr2 := "SELECT id, title, author, price, sales, stock, img_path FROM books where price between ? and ? limit ?,?"
	rows, err := utils.Db.Query(sqlStr2, minPrice, maxPrice, (iPageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	var books []*model.Book //定义book切片存放当前页数据
	for rows.Next() {
		book := &model.Book{} //单条信息读取
		rows.Scan(&book.ID, &book.Title, &book.Author, &book.Price, &book.Sales, &book.Stock, &book.ImgPath)
		books = append(books, book) //单条信息写入
	}

	//最后把书籍信息及相关页数总数信息写入page结构体
	page := &model.Page{
		Books:       books,
		PageNo:      iPageNo,
		PageSize:    pageSize,
		TotalPageNo: totalPageNo,
		TotalRecord: totalRecord,
	}
	return page, nil
}
